﻿-- Creator:       MySQL Workbench 5.2.34/ExportSQLServer plugin 2011.07.15
-- Author:        Keith Martin
-- Caption:       New Model
-- Project:       SSIS Metadata Database Model
-- Changed:       2012-09-07 23:09
-- Created:       2012-04-29 15:54

-- Schema: dbo


CREATE TABLE [dbo].[Domains](
  [Domain] VARCHAR(50) CONSTRAINT [pk_Domains] PRIMARY KEY NOT NULL,
  [DomainDesc] VARCHAR(255)
);


CREATE TABLE [dbo].[RunScan](
  [RunKey] INTEGER CONSTRAINT [pk_RunScan] PRIMARY KEY NOT NULL,
  [RunDate] DATETIME NOT NULL,
  [RunCommand] VARCHAR(4000) NOT NULL
);


CREATE TABLE [dbo].[mdExecutions](
--   Stores a row for each Iniital Load execution of the Meta Data Collection
  [ExecutionID] INTEGER CONSTRAINT [pk_mdExecutions] PRIMARY KEY IDENTITY NOT NULL,
  [CommandLine] VARCHAR(4000) NOT NULL,
  [ExecutionDate] DATETIME NOT NULL
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores a row for each Iniital Load execution of the Meta Data Collection', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdExecutions';


CREATE TABLE [dbo].[mdReportServers](
--   Stores all the reporting Servers that were scanned
  [ReportServerID] INTEGER CONSTRAINT [pk_mdReportServers] PRIMARY KEY NOT NULL,
  [ReportServerURL] VARCHAR(128) NOT NULL,
  [mdExecutions_ExecutionID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdReportServers_mdExecutions1]
    FOREIGN KEY([mdExecutions_ExecutionID])
    REFERENCES [mdExecutions]([ExecutionID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the reporting Servers that were scanned', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdReportServers';
CREATE INDEX [mdReportServers.fk_mdReportServers_mdExecutions1] ON [mdReportServers]([mdExecutions_ExecutionID]);


CREATE TABLE [dbo].[mdReports](
--   Stores all the reports on a Reporting Server
  [ReportID] INTEGER CONSTRAINT [pk_mdReports] PRIMARY KEY NOT NULL,
  [ReportURL] VARCHAR(512) NOT NULL,
  [mdReportServers_ReportServerID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdReports_mdReportServers1]
    FOREIGN KEY([mdReportServers_ReportServerID])
    REFERENCES [mdReportServers]([ReportServerID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the reports on a Reporting Server', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdReports';
CREATE INDEX [mdReports.fk_mdReports_mdReportServers1] ON [mdReports]([mdReportServers_ReportServerID]);


CREATE TABLE [dbo].[mdObjectTypes](
--   Stores a list of Object Types
  [ObjectTypeID] INTEGER CONSTRAINT [pk_mdObjectTypes] PRIMARY KEY IDENTITY NOT NULL,
  [Name] VARCHAR(128)
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores a list of Object Types', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjectTypes';


CREATE TABLE [dbo].[mdDatabaseTypes](
--   Stores a list of Database Types.
  [DatabaseTypeID] INTEGER CONSTRAINT [pk_mdDatabaseTypes] PRIMARY KEY IDENTITY NOT NULL,
  [Name] VARCHAR(128) NOT NULL
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores a list of Database Types.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdDatabaseTypes';


CREATE TABLE [dbo].[mdFiles](
--   Stores all Files that are encountered in the MetaData
  [FileID] INTEGER CONSTRAINT [pk_mdFiles] PRIMARY KEY IDENTITY NOT NULL,
  [FileName] VARCHAR(128) NOT NULL,
  [mdExecutions_ExecutionID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdFiles_mdExecutions1]
    FOREIGN KEY([mdExecutions_ExecutionID])
    REFERENCES [mdExecutions]([ExecutionID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all Files that are encountered in the MetaData', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdFiles';
CREATE INDEX [mdFiles.fk_mdFiles_mdExecutions1] ON [mdFiles]([mdExecutions_ExecutionID]);


CREATE TABLE [dbo].[mdSSISPackages](
--   Stores all SSIS packages that are encountered in the MetaData
  [SSISPackageID] INTEGER CONSTRAINT [pk_mdSSISPackages] PRIMARY KEY IDENTITY NOT NULL,
  [PackageName] VARCHAR(128) NOT NULL,
  [mdExecutions_ExecutionID] INTEGER NOT NULL,
  [PackageDescription] VARCHAR(512),-- The first 512 characters of the package description.
  [PackageLocation] VARCHAR(512),-- The location that the package is in.
  [PackageGUID] VARCHAR(38),-- The GUID value for the SSIS Packages as a String
  CONSTRAINT [fk_mdPackages_mdExecutions1]
    FOREIGN KEY([mdExecutions_ExecutionID])
    REFERENCES [mdExecutions]([ExecutionID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all SSIS packages that are encountered in the MetaData', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISPackages';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The first 512 characters of the package description.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISPackages', @level2type=N'COLUMN', @level2name=N'PackageDescription';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The location that the package is in.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISPackages', @level2type=N'COLUMN', @level2name=N'PackageLocation';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The GUID value for the SSIS Packages as a String', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISPackages', @level2type=N'COLUMN', @level2name=N'PackageGUID';


CREATE TABLE [dbo].[ObjectTypes](
  [ObjectTypeKey] VARCHAR(255) CONSTRAINT [pk_ObjectTypes] PRIMARY KEY NOT NULL,
  [ObjectTypeName] VARCHAR(2555),
  [ObjectTypeDesc] VARCHAR(2000),
  [ObjectMetaType] VARCHAR(255),
  [Domain] VARCHAR(50) NOT NULL,
  [Container] BIT,-- Set to true if this ObjectType can be a Container of other ObjectTypes
  CONSTRAINT [fk_ObjectTypes_Domains]
    FOREIGN KEY([Domain])
    REFERENCES [Domains]([Domain])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Set to true if this ObjectType can be a Container of other ObjectTypes', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ObjectTypes', @level2type=N'COLUMN', @level2name=N'Container';
CREATE INDEX [ObjectTypes.fk_ObjectTypes_Domains] ON [ObjectTypes]([Domain]);


CREATE TABLE [dbo].[Objects](
  [RunKey] INTEGER NOT NULL,
  [ObjectKey] INTEGER NOT NULL,
  [ObjectName] VARCHAR(1000),
  [ObjectTypeKey] VARCHAR(255) NOT NULL,
  [ObjectDesc] VARCHAR(1000),
 CONSTRAINT [pk_Objects] PRIMARY KEY([RunKey],[ObjectKey]),
  CONSTRAINT [fk_Objects_RunScan1]
    FOREIGN KEY([RunKey])
    REFERENCES [RunScan]([RunKey]),
  CONSTRAINT [fk_Objects_ObjectTypes1]
    FOREIGN KEY([ObjectTypeKey])
    REFERENCES [ObjectTypes]([ObjectTypeKey])
);
CREATE INDEX [Objects.fk_Objects_RunScan1] ON [Objects]([RunKey]);
CREATE INDEX [Objects.fk_Objects_ObjectTypes1] ON [Objects]([ObjectTypeKey]);


CREATE TABLE [dbo].[ObjectDependencies](
  [RunKey] INTEGER NOT NULL,
  [SrcObjectKey] INTEGER NOT NULL,
  [TgtObjectKey] INTEGER NOT NULL,
  [DependencyType] VARCHAR(50) NOT NULL,
 CONSTRAINT [pk_ObjectDependencies] PRIMARY KEY([RunKey],[SrcObjectKey],[TgtObjectKey]),
  CONSTRAINT [fk_ObjectDependencies_ObjectsSrc]
    FOREIGN KEY([RunKey],[SrcObjectKey])
    REFERENCES [Objects]([RunKey],[ObjectKey]),
  CONSTRAINT [fk_ObjectDependencies_ObjectsTgt]
    FOREIGN KEY([RunKey],[TgtObjectKey])
    REFERENCES [Objects]([RunKey],[ObjectKey])
);
CREATE INDEX [ObjectDependencies.fk_ObjectDependencies_ObjectsSrc] ON [ObjectDependencies]([RunKey],[SrcObjectKey]);
CREATE INDEX [ObjectDependencies.fk_ObjectDependencies_ObjectsTgt] ON [ObjectDependencies]([RunKey],[TgtObjectKey]);


CREATE TABLE [dbo].[ObjectAttributes](
  [Objects_RunKey] INTEGER NOT NULL,
  [Objects_ObjectKey] INTEGER NOT NULL,
  [ObjectAttrName] VARCHAR(1000) NOT NULL,
  [ObjectAttrValue] VARCHAR(MAX),
 CONSTRAINT [pk_ObjectAttributes] PRIMARY KEY([Objects_RunKey],[Objects_ObjectKey],[ObjectAttrName]),
  CONSTRAINT [fk_ObjectAttributes_Objects1]
    FOREIGN KEY([Objects_RunKey],[Objects_ObjectKey])
    REFERENCES [Objects]([RunKey],[ObjectKey])
);


CREATE TABLE [dbo].[mdFileColumns](
--   Stores all the columns for a specific File
  [FileColumnID] INTEGER CONSTRAINT [pk_mdFileColumns] PRIMARY KEY IDENTITY NOT NULL,
  [mdFiles_FileID] INTEGER NOT NULL,
  [ColumnName] VARCHAR(128) NOT NULL,
  [DataType] VARCHAR(128),
  CONSTRAINT [fk_mdFileToColumnMaps_mdFiles1]
    FOREIGN KEY([mdFiles_FileID])
    REFERENCES [mdFiles]([FileID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the columns for a specific File', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdFileColumns';
CREATE INDEX [mdFileColumns.fk_mdFileToColumnMaps_mdFiles1] ON [mdFileColumns]([mdFiles_FileID]);


CREATE TABLE [dbo].[mdDatabaseServer](
--   Stores all the database servers that were encountered in the scan.
  [DatabaseServerID] INTEGER CONSTRAINT [pk_mdDatabaseServer] PRIMARY KEY IDENTITY NOT NULL,
  [ServerName] VARCHAR(128) NOT NULL,
  [mdExecutions_ExecutionID] INTEGER NOT NULL,
  [mdDatabaseTypes_DatabaseTypeID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdDatabaseServer_mdExecutions1]
    FOREIGN KEY([mdExecutions_ExecutionID])
    REFERENCES [mdExecutions]([ExecutionID]),
  CONSTRAINT [fk_mdDatabaseServer_mdDatabaseTypes1]
    FOREIGN KEY([mdDatabaseTypes_DatabaseTypeID])
    REFERENCES [mdDatabaseTypes]([DatabaseTypeID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the database servers that were encountered in the scan.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdDatabaseServer';
CREATE INDEX [mdDatabaseServer.fk_mdDatabaseServer_mdExecutions1] ON [mdDatabaseServer]([mdExecutions_ExecutionID]);
CREATE INDEX [mdDatabaseServer.fk_mdDatabaseServer_mdDatabaseTypes1] ON [mdDatabaseServer]([mdDatabaseTypes_DatabaseTypeID]);


CREATE TABLE [dbo].[mdSSISContainers](
--   Stores the Data Flows, and such like containers in the SSIS Package.  This is needed because Components within a data flow have unique names, but not within the entire package.
  [SSISContainerID] INTEGER CONSTRAINT [pk_mdSSISContainers] PRIMARY KEY IDENTITY NOT NULL,
  [ContainerName] VARCHAR(128) NOT NULL,
  [ContainerType] VARCHAR(128) NOT NULL,
  [mdSSISPackages_SSISPackageID] INTEGER NOT NULL,
  [ContainerDescription] VARCHAR(MAX),-- Contains the description of this SSIS Container.
  CONSTRAINT [IX_mdSSISContainers]
    UNIQUE([SSISContainerID],[ContainerName]),
  CONSTRAINT [fk_mdSSISContainers_mdSSISPackages1]
    FOREIGN KEY([mdSSISPackages_SSISPackageID])
    REFERENCES [mdSSISPackages]([SSISPackageID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the Data Flows, and such like containers in the SSIS Package.  This is needed because Components within a data flow have unique names, but not within the entire package.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISContainers';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains the description of this SSIS Container.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdSSISContainers', @level2type=N'COLUMN', @level2name=N'ContainerDescription';
CREATE INDEX [mdSSISContainers.fk_mdSSISContainers_mdSSISPackages1] ON [mdSSISContainers]([mdSSISPackages_SSISPackageID]);


CREATE TABLE [dbo].[mdDatabases](
--   Stores all Databases that are encountered in the MetaData
  [DatabaseID] INTEGER CONSTRAINT [pk_mdDatabases] PRIMARY KEY IDENTITY NOT NULL,
  [DatabaseName] VARCHAR(128) NOT NULL,
  [mdDatabaseServer_DatabaseServerID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdDatabases_mdDatabaseServer1]
    FOREIGN KEY([mdDatabaseServer_DatabaseServerID])
    REFERENCES [mdDatabaseServer]([DatabaseServerID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all Databases that are encountered in the MetaData', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdDatabases';
CREATE INDEX [mdDatabases.fk_mdDatabases_mdDatabaseServer1] ON [mdDatabases]([mdDatabaseServer_DatabaseServerID]);


CREATE TABLE [dbo].[mdComponents](
--   Stores all the components encountered within the SSIS Package
  [ComponentID] INTEGER CONSTRAINT [pk_mdComponents] PRIMARY KEY IDENTITY NOT NULL,
  [ComponentName] VARCHAR(128) NOT NULL,
  [ComponentType] VARCHAR(128) NOT NULL,
  [mdSSISPackages_SSISPackageID] INTEGER NOT NULL,
  [mdSSISContainers_SSISContainerID] INTEGER,
  [ComponentComment] VARCHAR(MAX),-- Contains the description of the component from the SSIS package.
  [ComponentQuery] VARCHAR(MAX),-- If this is an SQL Task, or Source, etc. then this may contain an SQL Statement.
  CONSTRAINT [fk_mdComponents_mdSSISPackages1]
    FOREIGN KEY([mdSSISPackages_SSISPackageID])
    REFERENCES [mdSSISPackages]([SSISPackageID]),
  CONSTRAINT [fk_mdComponents_mdSSISContainers1]
    FOREIGN KEY([mdSSISContainers_SSISContainerID])
    REFERENCES [mdSSISContainers]([SSISContainerID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the components encountered within the SSIS Package', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponents';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains the description of the component from the SSIS package.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponents', @level2type=N'COLUMN', @level2name=N'ComponentComment';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If this is an SQL Task, or Source, etc. then this may contain an SQL Statement.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponents', @level2type=N'COLUMN', @level2name=N'ComponentQuery';
CREATE INDEX [mdComponents.fk_mdComponents_mdSSISPackages1] ON [mdComponents]([mdSSISPackages_SSISPackageID]);
CREATE INDEX [mdComponents.fk_mdComponents_mdSSISContainers1] ON [mdComponents]([mdSSISContainers_SSISContainerID]);


CREATE TABLE [dbo].[mdObjects](
--   Stores all the objects within a database (Tables/Views, Procedures, Functions, DSViews etc.)
  [ObjectID] INTEGER CONSTRAINT [pk_mdObjects] PRIMARY KEY IDENTITY NOT NULL,
  [ObjectSchema] VARCHAR(128),-- String.Empty for SSAS, otherwise is populated with the Schema if known.
  [ObjectName] VARCHAR(128),-- The name of the Object.
  [mdObjectTypes_ObjectTypeID] INTEGER NOT NULL,
  [mdDatabases_DatabaseID] INTEGER NOT NULL,
  [ObjectComment] VARCHAR(MAX),-- Stores comments about the Object.  Usually Populated by SSAS Analyser.
  [ObjectQuery] VARCHAR(MAX),-- Stores the Query that caused this object to be created.  SSAS and SSIS and maybe SSRS populate this.
  CONSTRAINT [fk_mdFunctions_mdObjectTypes1]
    FOREIGN KEY([mdObjectTypes_ObjectTypeID])
    REFERENCES [mdObjectTypes]([ObjectTypeID]),
  CONSTRAINT [fk_mdObjects_mdDatabases1]
    FOREIGN KEY([mdDatabases_DatabaseID])
    REFERENCES [mdDatabases]([DatabaseID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the objects within a database (Tables/Views, Procedures, Functions, DSViews etc.)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjects';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'String.Empty for SSAS, otherwise is populated with the Schema if known.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjects', @level2type=N'COLUMN', @level2name=N'ObjectSchema';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the Object.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjects', @level2type=N'COLUMN', @level2name=N'ObjectName';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores comments about the Object.  Usually Populated by SSAS Analyser.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjects', @level2type=N'COLUMN', @level2name=N'ObjectComment';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the Query that caused this object to be created.  SSAS and SSIS and maybe SSRS populate this.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjects', @level2type=N'COLUMN', @level2name=N'ObjectQuery';
CREATE INDEX [mdObjects.fk_mdFunctions_mdObjectTypes1] ON [mdObjects]([mdObjectTypes_ObjectTypeID]);
CREATE INDEX [mdObjects.fk_mdObjects_mdDatabases1] ON [mdObjects]([mdDatabases_DatabaseID]);


CREATE TABLE [dbo].[mdObjectColumns](
--   Stores all the columns for a specific Object.
  [ObjectColumnID] INTEGER CONSTRAINT [pk_mdObjectColumns] PRIMARY KEY IDENTITY NOT NULL,
  [mdObjects_ObjectID] INTEGER NOT NULL,
  [ColumnName] VARCHAR(128) NOT NULL,
  [DataType] VARCHAR(128),
  CONSTRAINT [fk_mdObjectToColumnMap_mdObjects1]
    FOREIGN KEY([mdObjects_ObjectID])
    REFERENCES [mdObjects]([ObjectID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all the columns for a specific Object.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjectColumns';
CREATE INDEX [mdObjectColumns.fk_mdObjectToColumnMap_mdObjects1] ON [mdObjectColumns]([mdObjects_ObjectID]);


CREATE TABLE [dbo].[mdReportToObjectMap](
--   Store a mapping between the Report and the Objects that it uses.
  [ReportToObjectMapID] INTEGER CONSTRAINT [pk_mdReportToObjectMap] PRIMARY KEY IDENTITY NOT NULL,
  [mdReports_ReportID] INTEGER NOT NULL,
  [mdObjects_ObjectID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdReportToObjectMap_mdReports1]
    FOREIGN KEY([mdReports_ReportID])
    REFERENCES [mdReports]([ReportID]),
  CONSTRAINT [fk_mdReportToObjectMap_mdObjects1]
    FOREIGN KEY([mdObjects_ObjectID])
    REFERENCES [mdObjects]([ObjectID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Store a mapping between the Report and the Objects that it uses.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdReportToObjectMap';
CREATE INDEX [mdReportToObjectMap.fk_mdReportToObjectMap_mdReports1] ON [mdReportToObjectMap]([mdReports_ReportID]);
CREATE INDEX [mdReportToObjectMap.fk_mdReportToObjectMap_mdObjects1] ON [mdReportToObjectMap]([mdObjects_ObjectID]);


CREATE TABLE [dbo].[mdComponentToObjectMaps](
--   Stores the linkage between Components and Objects.
  [ComponentToObjectMapID] INTEGER CONSTRAINT [pk_mdComponentToObjectMaps] PRIMARY KEY IDENTITY NOT NULL,
  [mdComponents_ComponentID] INTEGER NOT NULL,
  [mdObjects_ObjectID] INTEGER NOT NULL,
  [isComponentToObject] BOOLEAN,-- Determines the direction of the connection.  Some components have a Table as a Source, others as a Target.
  CONSTRAINT [fk_mdComponentToObjectMaps_mdComponents1]
    FOREIGN KEY([mdComponents_ComponentID])
    REFERENCES [mdComponents]([ComponentID]),
  CONSTRAINT [fk_mdComponentToObjectMaps_mdObjects1]
    FOREIGN KEY([mdObjects_ObjectID])
    REFERENCES [mdObjects]([ObjectID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the linkage between Components and Objects.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentToObjectMaps';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determines the direction of the connection.  Some components have a Table as a Source, others as a Target.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentToObjectMaps', @level2type=N'COLUMN', @level2name=N'isComponentToObject';
CREATE INDEX [mdComponentToObjectMaps.fk_mdComponentToObjectMaps_mdComponents1] ON [mdComponentToObjectMaps]([mdComponents_ComponentID]);
CREATE INDEX [mdComponentToObjectMaps.fk_mdComponentToObjectMaps_mdObjects1] ON [mdComponentToObjectMaps]([mdObjects_ObjectID]);


CREATE TABLE [dbo].[mdComponentToComponentMaps](
--   Stores the linkages between components encountered in the SSIS package
  [ComponentToComponentMapID] INTEGER CONSTRAINT [pk_mdComponentToComponentMaps] PRIMARY KEY IDENTITY NOT NULL,
  [mdComponents_SourceComponentID] INTEGER NOT NULL,
  [mdComponents_TargetComponentID] INTEGER NOT NULL,
  CONSTRAINT [fk_mdComponentToComponentMaps_mdComponents1]
    FOREIGN KEY([mdComponents_SourceComponentID])
    REFERENCES [mdComponents]([ComponentID]),
  CONSTRAINT [fk_mdComponentToComponentMaps_mdComponents2]
    FOREIGN KEY([mdComponents_TargetComponentID])
    REFERENCES [mdComponents]([ComponentID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the linkages between components encountered in the SSIS package', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentToComponentMaps';
CREATE INDEX [mdComponentToComponentMaps.fk_mdComponentToComponentMaps_mdComponents1] ON [mdComponentToComponentMaps]([mdComponents_SourceComponentID]);
CREATE INDEX [mdComponentToComponentMaps.fk_mdComponentToComponentMaps_mdComponents2] ON [mdComponentToComponentMaps]([mdComponents_TargetComponentID]);


CREATE TABLE [dbo].[mdComponentToFileMaps](
--   Stores the linkage between Components and Files.
  [ComponentToFileMapID] INTEGER CONSTRAINT [pk_mdComponentToFileMaps] PRIMARY KEY IDENTITY NOT NULL,
  [mdComponents_ComponentID] INTEGER NOT NULL,
  [mdFiles_FileID] INTEGER NOT NULL,
  [isComponentToFile] BOOLEAN,-- Determines the direction of the connection.  Some components have a File as a Source, others as a Target.
  CONSTRAINT [fk_mdComponentToFileMaps_mdComponents1]
    FOREIGN KEY([mdComponents_ComponentID])
    REFERENCES [mdComponents]([ComponentID]),
  CONSTRAINT [fk_mdComponentToFileMaps_mdFiles1]
    FOREIGN KEY([mdFiles_FileID])
    REFERENCES [mdFiles]([FileID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the linkage between Components and Files.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentToFileMaps';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determines the direction of the connection.  Some components have a File as a Source, others as a Target.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentToFileMaps', @level2type=N'COLUMN', @level2name=N'isComponentToFile';
CREATE INDEX [mdComponentToFileMaps.fk_mdComponentToFileMaps_mdComponents1] ON [mdComponentToFileMaps]([mdComponents_ComponentID]);
CREATE INDEX [mdComponentToFileMaps.fk_mdComponentToFileMaps_mdFiles1] ON [mdComponentToFileMaps]([mdFiles_FileID]);


CREATE TABLE [dbo].[mdObjectToObjectMaps](
--   Stores the linkages between specific types of database objects.
  [ObjectToObjectMapID] INTEGER CONSTRAINT [pk_mdObjectToObjectMaps] PRIMARY KEY IDENTITY NOT NULL,
  [mdObjects_SourceObjectID] INTEGER NOT NULL,
  [mdObjects_TargetObjectID] INTEGER NOT NULL,
  [SourceIsContainer] BOOLEAN NOT NULL,-- This will be false for SQL Server databases, and true for some objects in SSAS databases.
  CONSTRAINT [fk_mdObjectToObjectMaps_mdObjects1]
    FOREIGN KEY([mdObjects_SourceObjectID])
    REFERENCES [mdObjects]([ObjectID]),
  CONSTRAINT [fk_mdObjectToObjectMaps_mdObjects2]
    FOREIGN KEY([mdObjects_TargetObjectID])
    REFERENCES [mdObjects]([ObjectID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the linkages between specific types of database objects.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjectToObjectMaps';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This will be false for SQL Server databases, and true for some objects in SSAS databases.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdObjectToObjectMaps', @level2type=N'COLUMN', @level2name=N'SourceIsContainer';
CREATE INDEX [mdObjectToObjectMaps.fk_mdObjectToObjectMaps_mdObjects1] ON [mdObjectToObjectMaps]([mdObjects_SourceObjectID]);
CREATE INDEX [mdObjectToObjectMaps.fk_mdObjectToObjectMaps_mdObjects2] ON [mdObjectToObjectMaps]([mdObjects_TargetObjectID]);


CREATE TABLE [dbo].[mdComponentColumns](
--   Stores any column that is CREATED within the SSIS Component
  [ComponentColumnID] INTEGER CONSTRAINT [pk_mdComponentColumns] PRIMARY KEY IDENTITY NOT NULL,
  [mdComponents_ComponentID] INTEGER NOT NULL,
  [ColumnName] VARCHAR(128) NOT NULL,
  [DataType] VARCHAR(128),
  [Expression] VARCHAR(MAX),-- Stores the expression that created this column
  CONSTRAINT [fk_mdComponentColumns_mdComponents1]
    FOREIGN KEY([mdComponents_ComponentID])
    REFERENCES [mdComponents]([ComponentID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores any column that is CREATED within the SSIS Component', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentColumns';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the expression that created this column', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentColumns', @level2type=N'COLUMN', @level2name=N'Expression';
CREATE INDEX [mdComponentColumns.fk_mdComponentColumns_mdComponents1] ON [mdComponentColumns]([mdComponents_ComponentID]);


CREATE TABLE [dbo].[mdComponentColumnToComponentColumnMaps](
--   Stores the mapping between two Columns
  [mdComponentColumnToComponentColumnMapsID] INTEGER CONSTRAINT [pk_mdComponentColumnToComponentColumnMaps] PRIMARY KEY NOT NULL,
  [mdComponentColumns_SourceComponentColumnID] INTEGER NOT NULL,
  [mdComponentColumns_TargetComponentColumnID] INTEGER NOT NULL,
  [Expression] VARCHAR(MAX),-- Stores the expression (if available), that maps between these columns.
  CONSTRAINT [fk_mdComponentColumnToComponentColumnMaps_mdComponentColumns1]
    FOREIGN KEY([mdComponentColumns_SourceComponentColumnID])
    REFERENCES [mdComponentColumns]([ComponentColumnID]),
  CONSTRAINT [fk_mdComponentColumnToComponentColumnMaps_mdComponentColumns2]
    FOREIGN KEY([mdComponentColumns_TargetComponentColumnID])
    REFERENCES [mdComponentColumns]([ComponentColumnID])
);

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the mapping between two Columns', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentColumnToComponentColumnMaps';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores the expression (if available), that maps between these columns.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'mdComponentColumnToComponentColumnMaps', @level2type=N'COLUMN', @level2name=N'Expression';
CREATE INDEX [mdComponentColumnToComponentColumnMaps.fk_mdComponentColumnToComponentColumnMaps_mdComponentColumns1] ON [mdComponentColumnToComponentColumnMaps]([mdComponentColumns_SourceComponentColumnID]);
CREATE INDEX [mdComponentColumnToComponentColumnMaps.fk_mdComponentColumnToComponentColumnMaps_mdComponentColumns2] ON [mdComponentColumnToComponentColumnMaps]([mdComponentColumns_TargetComponentColumnID]);
